import pymysql
connect = pymysql.connect(
    host='127.0.0.1',
    db='study',
    user='root',
    password='mysql',
    port=3306,
    charset='utf8', )
# 创建游标
cur = connect.cursor()


def result(x):
    for row in x:
        print(row)


# 1.内连接 inner join  on ：以两个表为基准，将两张表相对应的部分查询出来
sql_inner = 'select s.name,t.name from teachers as t  INNER JOIN students as s on t.namenumber = s.cno order by t.age'
cur.execute(sql_inner)
print('查询老师的名字编号与学生的学号相等且按照老师的年龄升序排列的老师名字及学生名字:')
result(cur.fetchall())


# 2.左连接：left join on :将左表中所有的信息查询出来，右表与左表关联的信息查出来

sql_left = 'select t.*, s.name from teachers as t  LEFT JOIN students as s on t.namenumber = s.cno '
cur.execute(sql_left)
print('查询老师的名字编号与学生的学号相等的老师信息及学生名字:')
result(cur.fetchall())

# 3.右连接： right join  on:将右表中所有的信息查询出来，左表与右表关联的信息查出来
sql_right = 'select t.*, s.name from teachers as t  right JOIN students as s on t.namenumber = s.cno '
cur.execute(sql_right)
print('查询老师的名字编号与学生的学号相等的老师信息及学生名字:')
result(cur.fetchall())

# 4子查询：一个select语句中包含另一个完整的select语句。
# 子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
sql_sql = 'select name from Student where age>(select age from teachers where name ="张三")'
cur.execute(sql_sql)
print('查询学生年龄比张三老师大的学生姓名;')
result(cur.fetchall())

